﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Data.Odbc;


public static class ConnectionStringExtensions
	{
		/// <summary>
		/// Generates and sets the connection string
		/// From BReusable
		/// </summary>
		/// <param name="cn"></param>
		/// <param name="address"></param>
		/// <param name="db"></param>
		/// <returns></returns>
		public static SqlConnection GenerateConnectionString(this SqlConnection cn, string address, string db)
		{
			cn.ConnectionString = "Data Source=" + address + ";Initial Catalog=" + db + ";Integrated Security=SSPI;";
			return cn;
		}

		/// <summary>
		/// From BReusable
		/// </summary>
		/// <param name="cn"></param>
		/// <param name="filePath"></param>
		/// <param name="readOnly"></param>
		/// <param name="firstRowHasColumnNames"></param>
		/// <returns></returns>
		public static OdbcConnection GenerateExcel2003ConnectionString(
			this OdbcConnection cn, String filePath, bool readOnly, bool firstRowHasColumnNames)
		{
			var connectionString = new StringBuilder("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + filePath + ";");
			if (readOnly) connectionString.Append(";ReadOnly=0");
			//This line was from OLE instructions, may not work right
			if (firstRowHasColumnNames) connectionString.Append(";HDR=Yes");
			if (connectionString[connectionString.Length - 1] != ';') connectionString.Append(";");
			cn.ConnectionString = connectionString.ToString();
			return cn;
		}


		/// <summary>
		/// Uses Microsoft.Jet.OLEDB.4.0
		/// From BReusable
		/// </summary>
		/// <param name="cn"></param>
		/// <param name="filePath">absolute or relative file path to the excel doc</param>
		/// <param name="readOnly">open as read only?
		/// adds to string:ReadOnly=0</param>
		/// <param name="firstRowHasColumnNames">does the first row of the data contain column names?
		/// adds to string:HDR=Yes</param>
		/// <param name="intermixedDataTypes">is text mixed with numbers in a column?</param>
		/// <returns></returns>
		public static System.Data.OleDb.OleDbConnection GenerateExcel2003ConnectionString(
			this System.Data.OleDb.OleDbConnection cn, String filePath, bool readOnly, bool firstRowHasColumnNames
			, bool intermixedDataTypes)
		{
			var connectionString=new StringBuilder("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
				+filePath+ @";Extended Properties=""Excel 8.0;");
			if(readOnly) connectionString.Append(";ReadOnly=0");
			if(firstRowHasColumnNames) connectionString.Append(";HDR=Yes");
			if(intermixedDataTypes) connectionString.Append(";IMEX=1");
			connectionString.Append(@""";");
			cn.ConnectionString = connectionString.ToString();
			return cn;
		}

		/// <summary>
		/// Generates and sets the connection string for connecting to teradata
		/// From BReusable
		/// </summary>
		/// <param name="cn"></param>
		/// <param name="server"></param>
		/// <param name="database"></param>
		/// <param name="uid"></param>
		/// <param name="pwd"></param>
		/// <returns></returns>
		public static OdbcConnection SetTeraConnectionString(this OdbcConnection
		cn, String server, String database, String uid, String pwd)
		{
			cn.ConnectionString = "Driver={Teradata}; Provider=Teradata;DBCName=" + server +
				";Data Source=" + server + ";Database=" + database + ";Uid=" + uid + ";Pwd=" + pwd;
			return cn;
		}

		

	}
